package com.lgl.mes.common.util.org.easy.excel.util;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import com.lgl.mes.common.util.org.easy.excel.exception.ExcelDataException;
import com.lgl.mes.common.util.org.easy.excel.exception.ExcelException;
import org.springframework.core.io.ClassPathResource;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.text.NumberFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;

/**
 * Excel 操作工具类
 * @author lisuo
 *
 */
public abstract class ExcelUtil {
	
	/**
	 * @date 2021/08/09 <br/>
	 * 用于处理cell double精度问题（例：3458.84在excel中为number类型时，
	 * 通过cell.getNumericCellValue()获取到的value是(3458.839999999999)，精度并不准确），
	 * 而NumberFormat类的实例并不是线程安全的，所以这里使用ThreadLocal作为缓存确保性能
	 */
	private static ThreadLocal<NumberFormat> numberFormatThreadLocal = new ThreadLocal<NumberFormat>() {
		protected NumberFormat initialValue() {
			NumberFormat numberFormat = NumberFormat.getInstance();
			numberFormat.setGroupingUsed(false);
			return numberFormat;
		}
	};
	
	
	/**
	 * 读取Excel,支持任何不规则的Excel文件,
	 * 外层List表示所有的数据行，内层List表示每行中的cell单元数据位置
	 * 假设获取一个Excel第三行第二个单元格的数据，例子代码：
	 * FileInputStream excelStream = new FileInputStream(path);
	 * List<List<Object>> list = ExcelUtil.readExcel(excelStream);
	 * System.out.println(list.get(2).get(1));//第三行第二列,索引行位置是2,列的索引位置是1
	 * @param excelStream Excel文件流
	 * @param sheetIndex Excel-Sheet 的索引
	 * @return List<List<Object>> 
	 * @throws Exception
	 */
	public static List<List<Object>> readExcel(InputStream excelStream,int sheetIndex){
		List<List<Object>> datas = new ArrayList<List<Object>>();
		Workbook workbook = getWorkBookByStream(excelStream);
		try {
			Sheet sheet = getSheetAt(workbook, sheetIndex);
			int rows = sheet.getPhysicalNumberOfRows();
			for (int i = 0; i < rows; i++) {
				Row row = sheet.getRow(i);
				if(row==null){
					continue;
				}
				//为-1时,行没有任何单元格数据
				short cellNum = row.getLastCellNum();
				if(cellNum > 0) {
					List<Object> item = new ArrayList<Object>(cellNum);
					for(int j=0;j<cellNum;j++){
						Object value = ExcelUtil.getCellValue(row.getCell(j));
						item.add(value);
					}
					datas.add(item);
				}
			}
			return datas;
		}finally {
			ExcelUtil.closeBook(workbook);
		}
	}
	
	/**
	 * 读取Excel,支持任何不规则的Excel文件,默认读取第一个sheet页
	 * 外层List表示所有的数据行，内层List表示每行中的cell单元数据位置
	 * 假设获取一个Excel第三行第二个单元格的数据，例子代码：
	 * FileInputStream excelStream = new FileInputStream(path);
	 * List<List<Object>> list = ExcelUtil.readExcel(excelStream);
	 * System.out.println(list.get(2).get(1));//第三行第二列,索引行位置是2,列的索引位置是1
	 * @param excelStream Excel文件流
	 * @return List<List<Object>> 
	 * @throws Exception
	 */
	public static List<List<Object>> readExcel(InputStream excelStream)throws Exception {
		return readExcel(excelStream,0);
	}
	
	/**
	 * 设置Cell单元的值
	 * 
	 * @param cell
	 * @param value
	 */
	public static void setCellValue(Cell cell, Object value) {
		if (value != null) {
			if (value instanceof String) {
				cell.setCellValue((String) value);
			} else if (value instanceof Number) {
				if(value instanceof Double) {
					cell.setCellValue((Double)value);
				}else {
					cell.setCellValue(Double.parseDouble(String.valueOf(value)));
				}
			} else if (value instanceof Boolean) {
				cell.setCellValue((Boolean) value);
			} else if (value instanceof Date) {
				cell.setCellValue((Date) value);
			}else if(value instanceof RichTextString){
				cell.setCellValue((RichTextString)value);
			}else if(value instanceof Calendar) {
				cell.setCellValue((Calendar)value);
			} else if(value instanceof LocalDate) {
				cell.setCellValue((LocalDate)value);
			}else if(value instanceof LocalDateTime) {
				cell.setCellValue((LocalDateTime)value);
			}else {
				cell.setCellValue(value.toString());
			}
		}
	}
	
	/**
	 * 获取cell值
	 * 
	 * @param cell
	 * @return
	 */
	public static Object getCellValue(Cell cell) {
		Object value = null;
		if (null != cell) {
			switch (cell.getCellType()) {
			// 空白
			case BLANK:
				break;
			// Boolean
			case BOOLEAN:
				value = cell.getBooleanCellValue();
				break;
			// 错误格式
			case ERROR:
				break;
			// 公式
			case FORMULA:
				try {
					Workbook wb = cell.getSheet().getWorkbook();
					CreationHelper crateHelper = wb.getCreationHelper();
					FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
					value = getCellValue(evaluator.evaluateInCell(cell));
				}catch(Exception e) {
					//如果公式解析出错，则按字符处理。获取内容
					value = cell.getStringCellValue();
				}
				break;
			// 数值
			case NUMERIC:
				// 处理日期格式
				if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
					value = cell.getDateCellValue();
				} else {
					value = numberFormatThreadLocal.get().format(cell.getNumericCellValue());
				}
				break;
			case STRING:
				value = cell.getStringCellValue();
				break;
			default:
			}
		}
		return value;
	}
	
	public static Sheet getSheetAt(Workbook workbook,int sheetIndex){
		try{
			return workbook.getSheetAt(sheetIndex);
		}catch(Exception e){
			throw new ExcelException("找不到对应的sheet页");
		}
	}
	
	/**
	 * 关闭workbook
	 * @param workbook
	 */
	public static void closeBook(Workbook workbook) {
		try {
			if(workbook!=null) {
				workbook.close();
			}
		}catch(Exception ignore) {}
	}
	
	/**
	 * 通过文件流获取workbook实例
	 * @param excelStream excel文件流
	 * @return Workbook
	 * @throws Exception
	 */
	public static Workbook getWorkBookByStream(InputStream excelStream){
		try{
			return WorkbookFactory.create(excelStream);
		}catch(Exception e){
			if(excelStream!=null){
				try{
					//文件格式异常POI框架不会关闭资源导致文件一直被当前应用占用，释放文件资源
					excelStream.close();
				}catch(Exception ignore){}
			}
			if(e instanceof IOException) {
				if("Your InputStream was neither an OLE2 stream, nor an OOXML stream".equals(e.getMessage())) {
					throw new ExcelException("导入的文件不是Excel,无法操作");
				}
			}
			if(e instanceof EncryptedDocumentException) {
				throw new ExcelException("导入的文件是加密的,无法操作"); 
			}
			throw new ExcelException(e.getMessage()); 
		}
	}
	
	/**
	 * 通过路径获取workbook实例
	 * @param path 路径：支持，http、classpath，绝对路径
	 * @return
	 * @throws Exception
	 */
	public static Workbook getWorkBookByPath(String path){
		if(StringUtils.isBlank(path)){
			return null;
		}
		try{
			if (path.startsWith("http:")) {
				URL url = new URL(path);
				return getWorkBookByStream(url.openStream());
			}else if(path.startsWith("classpath:")){
				ClassPathResource resource = new ClassPathResource(path.replace("classpath:", ""));
				return getWorkBookByStream(resource.getInputStream());
			}else{
				return getWorkBookByStream(new FileInputStream(new File(path)));
			}
		}catch(Exception e){
			if(e instanceof ExcelException){
				throw (ExcelException)e;
			}
			throw new ExcelException(e);
		}
	}
	
	/**
	 * 向指定的位置写入数据
	 * @param workbook excel
	 * @param sheetIndex sheet索引位
	 * @param rownum 行
	 * @param cellnum 列
	 * @param value 值
	 * @param append 是否追加
	 * @param symbol 追加数据使用的符号
	 */
	public static void write(Workbook workbook,int sheetIndex,int rownum,int cellnum,String value,boolean append,String symbol){
		Cell cell = getSheetAt(workbook, sheetIndex).getRow(rownum).getCell(cellnum);
		if(cell==null){
			cell = getSheetAt(workbook, sheetIndex).getRow(rownum).createCell(cellnum);
		}
		String val = value;
		if(append){
			Object cellValue = getCellValue(cell);
			if(cellValue!=null){
				if(symbol!=null && !cell.toString().trim().equals("")){
					val = cellValue.toString()+symbol+value;
				}else{
					val = cellValue.toString()+value;
				}
			}
		}
		setCellValue(cell, val);
	}
	
	/**
	 * 场景：获取Excel上传失败生成Excel文件描述文件
	 * @param errorList 错误描述
	 * @param width 错误列宽度
	 * @return
	 */
	public static Workbook getErrorWorkbook(List<ExcelDataException> errorList,int width) {
		if(CollectionUtils.isEmpty(errorList)) {
			return null;
		}
		Workbook wk = new SXSSFWorkbook();
		Sheet sheet = wk.createSheet("失败原因列表");
		sheet.setColumnWidth(0, width);
		setCellValue(sheet.createRow(0).createCell(0), "失败原因");
		for (int i = 1; i <= errorList.size(); i++) {
			Row row = sheet.createRow(i);
			setCellValue(row.createCell(0), errorList.get(i-1).getMessage());
		}
		return wk;
	}
	
	/**
	 * 场景：Excel上传失败生成Excel文件描述，在每行的末尾增加：状态、失败原因
	 * @param errorList 错误描述
	 * @param excelStream 原Excel上传的文件
	 * @param sheetIndex sheet页索引位置
	 * @param titleIndex 标题索引位置
	 * @param removeSuccess 如果使用的是原生的导入模板数据，是否删除没有失败的行数据
	 * @return 错误的Excel描述文件
	 */
	public static Workbook getErrorWorkbook(List<ExcelDataException> errorList, InputStream excelStream,int sheetIndex,int titleIndex,boolean removeSuccess){
		if(CollectionUtils.isEmpty(errorList)) {
			return null;
		}
		if(excelStream!=null){
			Set<Integer> errSetNum = new HashSet<>(errorList.size());
			Workbook wk = ExcelUtil.getWorkBookByStream(excelStream);
			Sheet sheet = getSheetAt(wk, sheetIndex);
			Row startRow = sheet.getRow(titleIndex);
			int startRowNum = startRow.getRowNum();
			int lastCellnum = startRow.getLastCellNum();
			Cell lastCell = startRow.getCell(lastCellnum-1);
			int statusCellIndex = 0;
			if("失败原因".equals(ExcelUtil.getCellValue(lastCell))){
				statusCellIndex = lastCellnum-2;
				//写错误信息
				for (ExcelDataException err:errorList) {
					int rownum = startRowNum+err.getRow();
					if(!errSetNum.contains(rownum)) {
						errSetNum.add(rownum);
						ExcelUtil.write(wk, sheetIndex, rownum, statusCellIndex+1, "", false,null);
					}
					ExcelUtil.write(wk, sheetIndex, rownum, statusCellIndex, "失败", false, null);
					ExcelUtil.write(wk, sheetIndex, rownum, statusCellIndex+1, "["+err.getTitle()+"]"+err.getErrInfo(), true, "。");
				}
			}else{
				statusCellIndex = lastCellnum;
				//写标题
				ExcelUtil.write(wk, sheetIndex, startRowNum, statusCellIndex, "处理结果", false, null);
				ExcelUtil.write(wk, sheetIndex, startRowNum, statusCellIndex+1, "失败原因", false, null);
				//写错误信息
				for (ExcelDataException err:errorList) {
					int rownum = startRowNum+err.getRow();
					errSetNum.add(rownum);
					ExcelUtil.write(wk, sheetIndex, rownum, statusCellIndex, "失败", false, null);
					ExcelUtil.write(wk, sheetIndex, rownum, statusCellIndex+1, "["+err.getTitle()+"]"+err.getErrInfo(), true, "。");
				}
			}
			if(removeSuccess) {
				//处理成功提示，状态列为空的全部认为成功
				for (Integer i = 1+startRowNum;i <= sheet.getLastRowNum(); i++) {
					if(!errSetNum.contains(i)) {
						//删除处理成功的数据
						Row row = sheet.getRow(i);
						sheet.removeRow(row);
					}
				}
				for (Integer i = 0;i <= sheet.getLastRowNum(); i++) {
					Row row = sheet.getRow(i);
					if(row == null) {
						sheet.shiftRows(i+1, sheet.getLastRowNum(), -1);
					}
				}
			}else {
				for (Integer i = 1+startRowNum;i <= sheet.getLastRowNum(); i++) {
					if(!errSetNum.contains(i)) {
						//处理成功数据的结果
						ExcelUtil.write(wk, sheetIndex, i, statusCellIndex, "成功", false, null);
					}
				}
			}
			return wk;
		}else {
			return getErrorWorkbook(errorList,15000);
		}
	}
	
}
